/**
 * Created by gaomeng on 2017/1/15.
 */
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public static void JDBC(String[] args){
        String driver="com.mysql.jdbc.Driver";//驱动程序名
        String url="jdbc:mysql://127.0.0.1:3306/Bsite";//URL指向要访问的数据库名
        String user="root";//MySQL配置时的用户名
        String password="root";//MySQL配置时的密码
        }
        try {
        Class.forName(driver);//加载驱动程序
        Connection conn = DriverManager.getConnection(url, user, password);
        } //连续驱动程序
        catch (SQLException e){
        e.printStackTrace();
        }
        return conn;
        }

//设置修改mysql中关于B站信息的储存
public static void insert(Connection conn, Bsite bsite) throws SQLException {
        String sql = "INSERT INTO bsite (author,title,favorite,url) VALUES (?,?,?,?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, bilibili.getAuthor());
        ps.setString(2, bilibili.getTitle());
        ps.setInt(3, bilibili.getFavorite());
        ps.setString(4, bilibili.getUrl());

        ps.execute();
        conn.close();
        }

//设置修改关于mysql中关于类别中前三名的视频
public static List<Bsite> searchTop3(Connection conn, String categories) throws SQLException{
        List<Bsite> topVideo = new ArrayList<Bsite>();
        try {
        conn = getConnection();
        Statement stmt = conn.createStatement();
        String sql = "select * from info where(categories ='" + categories + "') ORDER BY favorite DESC ";//数据库排序
        ResultSet resultSet = stmt.executeQuery(sql);
        int i = 1;
        while (resultSet.next() && i <= 3) {
        Bilibili data = new Bilibili();
        data.setCategories(resultSet.getString("categories"));
        data.setAuthor(resultSet.getString("author"));
        data.setFavorite(resultSet.getInt("favorite"));

        //取消重复的视频
        if (topVideo.size() == 0) {
        data.setUrl(resultSet.getString("url"));
        } else {
        if (resultSet.getString("url").equals(topVideo.get(i - 2).getUrl())) {
        continue;
        } else {
        data.setUrl(resultSet.getString("url"));
        }
        }

        data.setTitle(resultSet.getString("title"));
        topVideo.add(data);
        i++;
        }
        } catch (SQLException e) {
        e.printStackTrace();
        }
        return topVideo;
        }
        }
